import pandas as pd
import pymysql

db = pymysql.connect(host='10.2.5.100', port=13306, user='root', password='kc@#2023++', db="kcdoc")
cursor = db.cursor()
if __name__ == '__main__':
    run_task = """
             SELECT task.plan_id,task.tr_id FROM kc_user_plan_task task LEFT JOIN kc_production_plan plan on task.plan_id = plan.id 
                WHERE left(plan.production_code,2) = 'JF' and task.state = 1 
                and task.user_no in (SELECT user_no FROM sys_user WHERE user_realname in  ('付鹏','张铮铮')) 
                GROUP BY task.plan_id,task.tr_id
             """
    cursor.execute(run_task)
    all_error = cursor.fetchall()
    for error in all_error:
        add_task_user = """
                INSERT INTO `kcdoc`.`kc_user_plan_task` ( `plan_id`, `user_no`, `tr_id`, `state`, `create_time`) VALUES ({0}, '17693', {1}, 1, '2023-05-17 16:15:08');
            """.format(error[0], error[1])
        print(add_task_user)

    run_two_task = """
          SELECT  task.task_name, task.task_desc,task.ref_id,task.plan_id,task.standard_time,task.real_end_time  FROM kc_user_task task  
            LEFT JOIN kc_process_trajectory tra on task.ref_id = tra.id 
            LEFT JOIN kc_production_plan plan on plan.id = task.plan_id
            WHERE tra.state = 1
             and task.executor in (SELECT user_no FROM sys_user WHERE user_realname in   ('付鹏','张铮铮'))
              GROUP BY task.task_name, task.task_desc,task.ref_id,task.plan_id,task.standard_time,task.real_end_time
          """
    cursor.execute(run_two_task)
    all_error = cursor.fetchall()
    for error in all_error:
        add_user = """INSERT INTO `kcdoc`.`kc_user_task` (  `task_name`, `task_desc`, `create_time`, `executor`, `assignor`, `ref_type`, `ref_id`, `end_time`, `time_consuming`, `plan_id`, `standard_time`, `real_start_time`, `real_end_time`, `real_consuming`, `real_status`, `task_status`) VALUES ( '{0}', '{1}', '2023-04-10 13:34:29', '17693', '00379', 1, '{2}', '2023-04-24 19:12:28', 1229879, {3}, {4}, NULL, '{5}', 0, 2, NULL);
             """.format(error[0], error[1], error[2], error[3], error[4], error[5])
        print(add_user)
